﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Infragistics.Win.UltraWinGrid;
using Infragistics.Documents.Excel;
using System.Globalization;

namespace POS_NEW
{
    public partial class frmKiemKho : Form
    {
        public frmKiemKho()
        {
            InitializeComponent();
        }
        public static DataSet ds1;
        #region "Tab xuất phiếu"
        DataTable tbKiemKhoXuatPhieu;
        public static DataTable tbKiemKhoCapNhat;
        string TenCty = "";
        int DoiTuong = 0;
        #region "Load"
        private void LoadComboboxNhomHH()
        {
            cboNhomHangHoa.DataSource = null;
            cboNhomHangHoa.Items.Add("----Xem tất cả----");
            DataTable dt = AccessDataLayer.ExecuteDT("spNhom_HangHoa_Phu_Select", CommandType.StoredProcedure  );
            DataRow dr = dt.NewRow();
            dr["Tên Nhóm Hàng Hóa Phụ"] = " ----Xem tất cả----";
            dr["Mã nhóm"] = -1;
            dt.Rows.Add(dr);
            cboNhomHangHoa.DataSource = dt;
            cboNhomHangHoa.DisplayMember = "Tên nhóm hàng hóa phụ";
            cboNhomHangHoa.ValueMember = "ID nhóm hàng hóa phụ";
            cboNhomHangHoa.DropDownStyle = Infragistics.Win.DropDownStyle.DropDownList;
            cboNhomHangHoa.SortStyle = Infragistics.Win.ValueListSortStyle.Ascending;
            cboNhomHangHoa.SelectedIndex = 0;
        }
        private void LoadgridKiemKho(int ID_NhomHangHoa_Phu)
        {
            DataTable dtKiemKho = AccessDataLayer.ExecuteDT("spKiemKho_Select", CommandType.StoredProcedure
                                                           , "@ID_Nhom_HangHoa_Phu", ID_NhomHangHoa_Phu
                                                           , "@ID_ChiNhanh", myProgress.ID_ChiNhanh);
            gridKiemKho_XuatPhieu.DataSource = dtKiemKho;

            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID Hàng hóa"].Hidden = true;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Số lô"].Hidden = true;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Ngày kiểm"].Hidden = true;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Giá tồn"].Hidden = true;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID kho"].Hidden = true;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID chi nhánh"].Hidden = true;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Chi nhánh"].Hidden = true;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Chênh lệch"].Hidden = true;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID nhóm hàng hóa phụ"].Hidden = true;
            gridKiemKho_XuatPhieu.DisplayLayout.Override.AllowDelete = Infragistics.Win.DefaultableBoolean.False;

            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Override.HeaderStyle = Infragistics.Win.HeaderStyle.Standard;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID Hàng hóa"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Mã hàng hóa"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Hàng hóa"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Đơn vị"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Giá tồn"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Ngày hết hạn"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["SL sổ sách"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["SL thực"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["SL kiểm"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Override.CellClickAction = CellClickAction.RowSelect;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Đơn vị"].CellAppearance.TextHAlign = Infragistics.Win.HAlign.Center;

            gridKiemKho_XuatPhieu.DisplayLayout.PerformAutoResizeColumns(true, Infragistics.Win.UltraWinGrid.PerformAutoSizeType.VisibleRows);
            gridKiemKho_XuatPhieu.DisplayLayout.AutoFitStyle = Infragistics.Win.UltraWinGrid.AutoFitStyle.ResizeAllColumns;
            gridKiemKho_XuatPhieu.Rows.ExpandAll(true);
            tbKiemKhoXuatPhieu = ((DataTable)gridKiemKho_XuatPhieu.DataSource);
            #region "Có số lô"
            //DataSet ds = AccessDataLayer.Execute("spKiemKho_Select", CommandType.StoredProcedure
            //                                                     , "@ID_Nhom_HangHoa_Phu", ID_NhomHangHoa_Phu
            //                                                     ,"@ID_ChiNhanh",myProgress.ID_ChiNhanh);

            //DataRelation dtr = new DataRelation("Relation", ds.Tables[0].Columns[0], ds.Tables[1].Columns[0], true);
            //ds.Relations.Add(dtr);
            //gridKiemKho_XuatPhieu.DataSource = ds;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Override.RowAppearance.BackColor = Color.Moccasin;

            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID Hàng hóa"].Hidden = true;
            ////gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns["ID Hàng hóa"].Hidden = true;
            ////gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns["Hàng hóa"].Hidden = true;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns["Ngày kiểm"].Hidden = true;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns["Giá tồn"].Hidden = true;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns["ID kho"].Hidden = true;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID chi nhánh"].Hidden = true;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns["ID chi nhánh"].Hidden = true;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Chi nhánh"].Hidden = true;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns["Chênh lệch"].Hidden = true;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID nhóm hàng hóa phụ"].Hidden = true;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns["Số lô"].Hidden = true;//-----------------------------Ẩn số lô-------------------------------------------------------
            //gridKiemKho_XuatPhieu.DisplayLayout.Override.AllowDelete = Infragistics.Win.DefaultableBoolean.False;

            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Override.HeaderStyle = Infragistics.Win.HeaderStyle.Standard;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns[0].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns[1].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns[2].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns[3].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns[4].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns[5].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Columns["SL kiểm"].Header.Appearance.BackColor = Color.LightSkyBlue;

            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Override.CellClickAction = Infragistics.Win.UltraWinGrid.CellClickAction.RowSelect;
            //gridKiemKho_XuatPhieu.DisplayLayout.Bands[1].Override.CellClickAction = CellClickAction.RowSelect;

            //gridKiemKho_XuatPhieu.DisplayLayout.PerformAutoResizeColumns(true, Infragistics.Win.UltraWinGrid.PerformAutoSizeType.VisibleRows);
            //gridKiemKho_XuatPhieu.DisplayLayout.AutoFitStyle = Infragistics.Win.UltraWinGrid.AutoFitStyle.ResizeAllColumns;
            //gridKiemKho_XuatPhieu.Rows.ExpandAll(true);
            //tbKiemKhoXuatPhieu = ((DataSet)gridKiemKho_XuatPhieu.DataSource).Tables[0];
            #endregion
        }
        #endregion
        #region "Search"
        private void cboNhomHangHoa_ValueChanged(object sender, EventArgs e)
        {

            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].ColumnFilters.ClearAllFilters();
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID nhóm hàng hóa phụ"].AllowRowFiltering = Infragistics.Win.DefaultableBoolean.False;
            if (Convert.ToInt32(cboNhomHangHoa.Value.ToString()) != -1)
            {
                int s = Convert.ToInt32(cboNhomHangHoa.Value.ToString());
                gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].ColumnFilters["ID nhóm hàng hóa phụ"].FilterConditions.Add(FilterComparisionOperator.Equals, s);
            }
        }
        #endregion
        private void cboNhomHangHoa_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                txtMatHang.Focus();
                txtMatHang.SelectAll();
            }
        }

        private void txtMatHang_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                btnTaoPhieu.Focus();
            }
        }
        private void gridKiemKho_BeforeCellActivate(object sender, Infragistics.Win.UltraWinGrid.CancelableCellEventArgs e)
        {
            e.Cell.Activation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
        }
        private void txtMatHang_ValueChanged(object sender, EventArgs e)
        {
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].ColumnFilters.ClearAllFilters();
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Hàng hóa"].AllowRowFiltering = Infragistics.Win.DefaultableBoolean.False;
            if (txtMatHang.Text.Trim() != "")
            {
                string s = txtMatHang.Text.Trim();
                gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].ColumnFilters["Hàng hóa"].FilterConditions.Add(FilterComparisionOperator.Contains, s);
            }
        }
        private void ulgridKiemKho_Excel_BeginExport(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.BeginExportEventArgs e)
        {
            //e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].Value = "A";
            //e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex + 1].Value = "B";
            //e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex + 2].Value = "C";

            //e.CurrentRowIndex++;
            //e.CurrentWorksheet.Rows[0].Cells[1].Value = TenCty;
            //e.CurrentWorksheet.Rows[0].Cells[1].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
            //e.CurrentWorksheet.Rows[0].Cells[1].CellFormat.Font.Italic = ExcelDefaultableBoolean.True;
            //e.CurrentWorksheet.Rows[0].Cells[1].CellFormat.Font.Color = Color.Blue;
            e.CurrentWorksheet.Rows[1].Cells[1].Value = "PHIẾU KIỂM KHO";
            e.CurrentWorksheet.Rows[1].Cells[1].CellFormat.Font.Bold = ExcelDefaultableBoolean.True;
            e.CurrentWorksheet.Rows[e.CurrentRowIndex].Cells[e.CurrentColumnIndex].CellFormat.Font.Name = "Tahoma";
            e.CurrentWorksheet.Rows[1].Cells[1].CellFormat.Font.Height = 300;
            e.CurrentWorksheet.Rows[1].Cells[1].Worksheet.MergedCellsRegions.Add(1, 1, 1, 8);
            e.CurrentWorksheet.Rows[2].Cells[7].Value = "Ngày : " ;
            e.CurrentWorksheet.Rows[2].Cells[7].CellFormat.Font.Italic = ExcelDefaultableBoolean.True;
            e.CurrentWorksheet.Rows[2].Cells[8].Value = DateTime.Now.ToString("dd/MM/yyyy");
            e.CurrentWorksheet.Rows[2].Cells[8].CellFormat.Font.Italic = ExcelDefaultableBoolean.True;
            e.CurrentWorksheet.Rows[2].Cells[1].Value = "ID chi nhánh : ";
            e.CurrentWorksheet.Rows[2].Cells[2].Value = ((DataTable)gridKiemKho_XuatPhieu.DataSource).Rows[0]["ID chi nhánh"].ToString();
            e.CurrentWorksheet.Rows[3].Cells[1].Value = "Chi nhánh :";
            e.CurrentWorksheet.Rows[3].Cells[2].Value = ((DataTable)gridKiemKho_XuatPhieu.DataSource).Rows[0]["Chi nhánh"].ToString();
            //e.CurrentWorksheet.Rows[3].Cells[1].Value = ((DataSet)gridKiemKho_XuatPhieu.DataSource).Tables[0].Rows[0]["Chi nhánh"].ToString(); Số lô
           
            e.CurrentWorksheet.Rows[1].Cells[2].CellFormat.Alignment = HorizontalCellAlignment.Center;
        }
        private void btnTaoPhieu_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Excel file|.xls";
            sfd.FileName = "Phieu_KK_" + DateTime.Now.Year + "" + DateTime.Now.Month + DateTime.Now.Day;
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    object[] obj = new object[2] { "@TABLENAME", "tbCongTy" };
                    DataTable dt = new DataTable("dt_congty");
                    dt = AccessDataLayer.ExecuteDT("spGetTable", CommandType.StoredProcedure, obj);
                    TenCty = dt.Rows[0][1].ToString();
                    Workbook wf = new Workbook();
                    gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID Hàng hóa"].Hidden = false;
                    gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Giá tồn"].Hidden = false;
                    gridKiemKho_XuatPhieu.DisplayLayout.PerformAutoResizeColumns(true, Infragistics.Win.UltraWinGrid.PerformAutoSizeType.VisibleRows);
                    gridKiemKho_XuatPhieu.DisplayLayout.AutoFitStyle = Infragistics.Win.UltraWinGrid.AutoFitStyle.ResizeAllColumns;
                    ulgridKiemKho_Excel.Export(gridKiemKho_XuatPhieu, wf, 5, 1);
                    wf.Save(sfd.FileName);

                    System.Diagnostics.Process.Start(sfd.FileName);
                }
                catch (Exception ex)
                {
                    using (ErrorScreen ers = new ErrorScreen(ex.Message))
                        ers.ShowDialog();
                }
            }
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["ID Hàng hóa"].Hidden = true;
            gridKiemKho_XuatPhieu.DisplayLayout.Bands[0].Columns["Giá tồn"].Hidden = true;
        }
        
        #endregion
        #region "Tab cập nhật"
        #region "Load"
        private void LoadcboNhomHang_CapNhat()
        {
            cboNhomHang_CapNhat.DataSource = cboNhomHangHoa.DataSource;
            
            cboNhomHang_CapNhat.DisplayMember = "Tên nhóm hàng hóa phụ";
            cboNhomHang_CapNhat.ValueMember = "ID nhóm hàng hóa phụ";
            cboNhomHang_CapNhat.DropDownStyle = Infragistics.Win.DropDownStyle.DropDownList;
            cboNhomHang_CapNhat.SortStyle = Infragistics.Win.ValueListSortStyle.Ascending;
            cboNhomHang_CapNhat.SelectedIndex = 0;
        }
        private void LoadgridKiemKho_CapNhat()
        {
            DataTable dtKiemKho = AccessDataLayer.ExecuteDT("spKiemKho_Select", CommandType.StoredProcedure
                                                              , "@ID_Nhom_HangHoa_Phu", -1
                                                              , "@ID_ChiNhanh", myProgress.ID_ChiNhanh);
            
            tbKiemKhoCapNhat = dtKiemKho;
            gridKiemKho_CapNhat.DataSource = tbKiemKhoCapNhat;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["ID Hàng Hóa"].Hidden = true;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Số lô"].Hidden = true;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Ngày kiểm"].Hidden = true;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Giá tồn"].Hidden = true;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["ID kho"].Hidden = true;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["ID nhóm hàng hóa phụ"].Hidden = true;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["ID chi nhánh"].Hidden = true;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Chi nhánh"].Hidden = true;
            gridKiemKho_CapNhat.DisplayLayout.Override.AllowDelete = Infragistics.Win.DefaultableBoolean.False;

            gridKiemKho_CapNhat.DisplayLayout.AutoFitStyle = Infragistics.Win.UltraWinGrid.AutoFitStyle.ResizeAllColumns;
            gridKiemKho_CapNhat.DisplayLayout.PerformAutoResizeColumns(true, Infragistics.Win.UltraWinGrid.PerformAutoSizeType.AllRowsInBand);
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Override.HeaderStyle = Infragistics.Win.HeaderStyle.Standard;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Mã hàng hóa"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Hàng hóa"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Đơn vị"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Giá tồn"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Ngày hết hạn"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["SL sổ sách"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["SL thực"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["SL kiểm"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Chênh lệch"].Header.Appearance.BackColor = Color.LightSkyBlue;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Chênh lệch"].CellAppearance.ForeColor = Color.Red;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Mã hàng hóa"].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Hàng hóa"].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Đơn vị"].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Ngày hết hạn"].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["SL thực"].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["SL sổ sách"].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["SL kiểm"].MaskInput = "nnnnnnnnnnnn.nn";
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Chênh lệch"].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Chênh lệch"].Format = "{0:0.##}";
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Đơn vị"].CellAppearance.TextHAlign = Infragistics.Win.HAlign.Center;
            #region "Có số lô"
            //DataSet  ds = AccessDataLayer.Execute("spKiemKho_Select", CommandType.StoredProcedure
            //                                                    , "@ID_Nhom_HangHoa_Phu", -1
            //                                                    , "@ID_ChiNhanh", myProgress.ID_ChiNhanh);
            //ds1 = ds;
            //DataRelation dtr = new DataRelation("Relation", ds.Tables[0].Columns[0], ds.Tables[1].Columns[0], true);
            //ds1.Relations.Add(dtr);
            //gridKiemKho_CapNhat.DataSource = ds;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[0].Override.RowAppearance.BackColor = Color.Moccasin;
           
            //gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["ID Hàng Hóa"].Hidden = true;
            ////gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["ID Hàng Hóa"].Hidden = true;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["Hàng Hóa"].Hidden = true;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["Ngày kiểm"].Hidden = true;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["Giá tồn"].Hidden = true;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["ID kho"].Hidden = true;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["ID chi nhánh"].Hidden = true;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["ID chi nhánh"].Hidden = true;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Chi nhánh"].Hidden = true;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["ID nhóm hàng hóa phụ"].Hidden = true;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["Số lô"].Hidden = true;//-----------------------------Ẩn số lô-------------------------------------------------------
            //gridKiemKho_CapNhat.DisplayLayout.Override.AllowDelete = Infragistics.Win.DefaultableBoolean.False;
            
            //gridKiemKho_CapNhat.DisplayLayout.AutoFitStyle = Infragistics.Win.UltraWinGrid.AutoFitStyle.ResizeAllColumns;
            //gridKiemKho_CapNhat.DisplayLayout.PerformAutoResizeColumns(true, Infragistics.Win.UltraWinGrid.PerformAutoSizeType.AllRowsInBand);
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Override.HeaderStyle = Infragistics.Win.HeaderStyle.Standard;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[0].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[1].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[2].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[3].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[4].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[5].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["SL kiểm"].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["Chênh lệch"].Header.Appearance.BackColor = Color.LightSkyBlue;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["Chênh lệch"].CellAppearance.ForeColor= Color.Red;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[0].Override.CellClickAction = Infragistics.Win.UltraWinGrid.CellClickAction.RowSelect;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[1].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[2].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[3].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[4].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit; 
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["SL thực"].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["SL kiểm"].MaskInput = "nnnnnnnnnnnn.nn";
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["Chênh lệch"].CellActivation = Infragistics.Win.UltraWinGrid.Activation.NoEdit;
            //gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns["Chênh lệch"].Format = "{0:0.##}";
            //gridKiemKho_CapNhat.Rows.ExpandAll(true);
            #endregion
        }
        #endregion
        #region "Search"
        private void cboNhomHang_CapNhat_ValueChanged(object sender, EventArgs e)
        {
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].ColumnFilters.ClearAllFilters();
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["ID nhóm hàng hóa phụ"].AllowRowFiltering = Infragistics.Win.DefaultableBoolean.False;
            if (Convert.ToInt32(cboNhomHang_CapNhat.Value.ToString()) != -1)
            {
                int s = Convert.ToInt32(cboNhomHang_CapNhat.Value.ToString());
                gridKiemKho_CapNhat.DisplayLayout.Bands[0].ColumnFilters["ID nhóm hàng hóa phụ"].FilterConditions.Add(FilterComparisionOperator.Equals, s);
            }
        }
        #endregion
        private void txtMatHang_CapNhat_ValueChanged(object sender, EventArgs e)
        {
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].ColumnFilters.ClearAllFilters();
            gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns["Hàng hóa"].AllowRowFiltering = Infragistics.Win.DefaultableBoolean.False;
            if (txtMatHang_CapNhat.Text.Trim() != "")
            {
                string s = txtMatHang_CapNhat.Text.Trim();
                gridKiemKho_CapNhat.DisplayLayout.Bands[0].ColumnFilters["Hàng hóa"].FilterConditions.Add(FilterComparisionOperator.Contains, s);
            }
        }
        private void btnCapNhat_Click(object sender, EventArgs e)
        {
           tbKiemKhoCapNhat=(DataTable)gridKiemKho_CapNhat.DataSource;
            gridKiemKho_CapNhat.PerformAction(UltraGridAction.CommitRow);
            using (frmKiemKho_XuatPhieu frm = new frmKiemKho_XuatPhieu())
            {
                if (frm.ShowDialog() == DialogResult.Cancel)
                    return;
                else
                {
                    try
                    {
                        foreach (DataRow dr in ((DataTable)gridKiemKho_CapNhat.DataSource).Rows)
                        {

                            dr["Ngày kiểm"] = dtNgay_Kiem_Kho.Value;
                            dr["ID kho"] = myProgress.ID_Kho;
                            dr["ID chi nhánh"] = myProgress.ID_ChiNhanh;
                            if (dr["SL kiểm"].ToString() == "")
                            {
                                dr["SL kiểm"] = dr["SL thực"];
                                dr["Chênh lệch"] = Convert.ToDouble(dr["SL thực"].ToString()) - Convert.ToDouble(dr["SL sổ sách"].ToString());
                            }
                        }
                        DataTable tbKiemKho = new DataTable();
                        var coltbKiemKho = new[] { "ID Hàng hóa", "Số lô", "SL kiểm", "SL sổ sách", "Chênh lệch", "Giá tồn", "Ngày kiểm", "ID Kho", "ID chi nhánh" };
                        tbKiemKho = ((DataTable)gridKiemKho_CapNhat.DataSource).DefaultView.ToTable(true, coltbKiemKho);


                        int kq = AccessDataLayer.ExecuteNonQuery("spKiemKho_Update", CommandType.StoredProcedure,
                                                                   "@tbKiemKho", tbKiemKho);
                        if (kq > 0)
                            using (ErrorScreen ers = new ErrorScreen("Cập nhật thành công", "Thông báo", 3))
                                ers.ShowDialog();
                    }
                    catch (Exception ex)
                    {
                        using (ErrorScreen ers = new ErrorScreen(ex.Message))
                        {
                            ers.ShowDialog();
                            return;
                        }
                    }
                    this.LoadgridKiemKho_CapNhat();
                }
            }
         
        }
        private void btnImportExcel_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Microsoft Office Excel 97-2003(*.xls)|*.xls|Microsoft Office Excel 2007(*.xlsx)|*.xlsx";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                DataTable tbImportEX = new DataTable();
                tbImportEX.Columns.Add("ID hàng hóa");
                tbImportEX.Columns.Add("Mã hàng hóa");
                tbImportEX.Columns.Add("Hàng hóa");
                tbImportEX.Columns.Add("Đơn vị");
                tbImportEX.Columns.Add("Giá tồn");
                tbImportEX.Columns.Add("Số lô");
                tbImportEX.Columns.Add("Ngày hết hạn");
                tbImportEX.Columns.Add("SL sổ sách");
                tbImportEX.Columns.Add("SL thực");
                tbImportEX.Columns.Add("SL kiểm");
                tbImportEX.Columns.Add("Chênh lệch");
                tbImportEX.Columns.Add("Ngày kiểm");
                tbImportEX.Columns.Add("ID kho");
                tbImportEX.Columns.Add("ID nhóm");
                tbImportEX.Columns.Add("ID chi nhánh");
                tbImportEX.Columns.Add("Chi nhánh");
                Workbook wb = Workbook.Load(ofd.FileName);
                DataRow dr;
                try
                {
                    for (int i = 6; i <= Convert.ToInt32(wb.Worksheets[0].Rows.Count().ToString())+1; i++)
                    {
                        dr = tbImportEX.NewRow();
                        dr["ID hàng hóa"] = wb.Worksheets[0].Rows[i].Cells[1].Value;
                        dr["Mã hàng hóa"] = wb.Worksheets[0].Rows[i].Cells[2].Value;
                        dr["Hàng hóa"] = wb.Worksheets[0].Rows[i].Cells[3].Value;
                        dr["Đơn vị"] = wb.Worksheets[0].Rows[i].Cells[4].Value;
                        dr["Số lô"] = myProgress.SoLo;
                        dr["SL kiểm"] = wb.Worksheets[0].Rows[i].Cells[9].Value;
                        dr["SL sổ sách"] = wb.Worksheets[0].Rows[i].Cells[7].Value;
                        if (wb.Worksheets[0].Rows[i].Cells[7].Value.ToString() != "" && wb.Worksheets[0].Rows[i].Cells[9].Value.ToString() != "")
                        {
                            dr["Chênh lệch"] = float.Parse(dr["SL kiểm"].ToString()) - float.Parse(dr["SL sổ sách"].ToString());
                        }

                        dr["SL thực"] = wb.Worksheets[0].Rows[i].Cells[8].Value;

                        dr["Giá tồn"] = wb.Worksheets[0].Rows[i].Cells[5].Value;
                        string NgayKiem = wb.Worksheets[0].Rows[2].Cells[8].Value.ToString().Replace("/", "");
                        String format = "ddMMyyyy";
                        DateTime result = DateTime.ParseExact(NgayKiem, format, CultureInfo.InvariantCulture);

                        dr["Ngày kiểm"] = result;
                        dr["ID kho"] = myProgress.ID_Kho;
                        dr["ID chi nhánh"] = wb.Worksheets[0].Rows[2].Cells[2].Value;
                        tbImportEX.Rows.Add(dr);
                    }
                    tbKiemKhoCapNhat = tbImportEX;
                }
                catch (Exception ex)
                {
                    using (ErrorScreen ers = new ErrorScreen(ex.Message))
                    {
                        ers.ShowDialog();
                        return;
                    }
                }
                using (frmKiemKho_XuatPhieu frm = new frmKiemKho_XuatPhieu())
                {
                    if (frm.ShowDialog() == DialogResult.Cancel)
                    {
                        return;
                    }
                    else
                    {
                        try
                        {
                            foreach (DataRow dr1 in tbKiemKhoCapNhat.Rows)
                            {
                                if (dr1["SL kiểm"].ToString().Trim() == "")
                                {
                                    dr1["SL kiểm"] = dr1["SL thực"];
                                    dr1["Chênh lệch"] = Convert.ToDouble(dr1["SL thực"].ToString()) - Convert.ToDouble(dr1["SL kiểm"].ToString());
                                }
                            }
                            //-----
                            DataTable tbKiemKho = new DataTable();
                            var coltbKiemKho = new[] { "ID Hàng hóa", "Số lô", "SL kiểm", "SL sổ sách", "Chênh lệch", "Giá tồn", "Ngày kiểm", "ID Kho", "ID chi nhánh" };
                            tbKiemKho = tbKiemKhoCapNhat.DefaultView.ToTable(true, coltbKiemKho);
                            int kq = AccessDataLayer.ExecuteNonQuery("spKiemKho_Update", CommandType.StoredProcedure,
                                                                                                  "@tbKiemKho", tbKiemKho);
                            if (kq > 0)
                                using (ErrorScreen ers = new ErrorScreen("Cập nhật thành công", "Thông báo", 3))
                                    ers.ShowDialog();
                        }
                        catch (Exception ex)
                        {
                            using (ErrorScreen ers = new ErrorScreen(ex.Message))
                            {
                                ers.ShowDialog();
                                return;
                            }
                        }
                        this.LoadgridKiemKho_CapNhat();
                    }
                }
            
            }
        }
        private void gridKiemKho_CapNhat_AfterRowUpdate(object sender, Infragistics.Win.UltraWinGrid.RowEventArgs e)
        {
            e.Row.Cells["Chênh lệch"].Value = Convert.ToDecimal(e.Row.Cells["SL kiểm"].Value.ToString()) - Convert.ToDecimal(e.Row.Cells["SL sổ sách"].Value.ToString());
            gridKiemKho_CapNhat.DisplayLayout.AutoFitStyle = Infragistics.Win.UltraWinGrid.AutoFitStyle.ResizeAllColumns;
            gridKiemKho_CapNhat.DisplayLayout.PerformAutoResizeColumns(true, Infragistics.Win.UltraWinGrid.PerformAutoSizeType.AllRowsInBand);
        }
        private void gridKiemKho_CapNhat_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter || e.KeyCode == Keys.Down)
                if (gridKiemKho_CapNhat.ActiveCell != null)
                    if (gridKiemKho_CapNhat.DisplayLayout.Bands[0].Columns[gridKiemKho_CapNhat.ActiveCell.Column.Key].Key == "SL kiểm")
                    {
                        gridKiemKho_CapNhat.ActiveRowScrollRegion.ScrollPosition = gridKiemKho_CapNhat.ActiveRowScrollRegion.ScrollPosition + 2;
                        int RowIndex = gridKiemKho_CapNhat.ActiveRow.Index;
                        int NextCell = gridKiemKho_CapNhat.ActiveCell.Row.Index + 1;
                        int RowCount = gridKiemKho_CapNhat.Rows.Count;
                        {
                            if (NextCell < RowCount)
                            {
                                gridKiemKho_CapNhat.ActiveCell = gridKiemKho_CapNhat.Rows[NextCell].Cells[gridKiemKho_CapNhat.ActiveCell.Column.Key];
                                gridKiemKho_CapNhat.Rows[NextCell].Cells[gridKiemKho_CapNhat.ActiveCell.Column.Key].Activation = Activation.AllowEdit;
                                gridKiemKho_CapNhat.Focus();
                                gridKiemKho_CapNhat.PerformAction(Infragistics.Win.UltraWinGrid.UltraGridAction.EnterEditMode, false, false);

                            }

                        }
                    }
            #region "Số lô"
            //if (e.KeyCode == Keys.Enter || e.KeyCode == Keys.Down)
            //    if (gridKiemKho_CapNhat.ActiveCell != null)
            //        if (gridKiemKho_CapNhat.DisplayLayout.Bands[1].Columns[gridKiemKho_CapNhat.ActiveCell.Column.Key].Key == "SL kiểm") 
            //        {
            //            gridKiemKho_CapNhat.ActiveRowScrollRegion.ScrollPosition = gridKiemKho_CapNhat.ActiveRowScrollRegion.ScrollPosition + 2;
            //            int ParentRowIndex = gridKiemKho_CapNhat.ActiveRow.ParentRow.Index;
            //            int NextCell = gridKiemKho_CapNhat.ActiveCell.Row.Index + 1;
            //            int childrowcount = gridKiemKho_CapNhat.Rows[ParentRowIndex].ChildBands[0].Rows.Count;
            //            int parentrowcount = gridKiemKho_CapNhat.Rows.Count;
            //            {
            //                if (NextCell < childrowcount)
            //                {
            //                    gridKiemKho_CapNhat.ActiveCell = gridKiemKho_CapNhat.Rows[ParentRowIndex].ChildBands[0].Rows[NextCell].Cells[gridKiemKho_CapNhat.ActiveCell.Column.Key];
            //                    gridKiemKho_CapNhat.Rows[ParentRowIndex].ChildBands[0].Rows[NextCell].Cells[gridKiemKho_CapNhat.ActiveCell.Column.Key].Activation = Activation.AllowEdit;
            //                    gridKiemKho_CapNhat.Focus();
            //                    gridKiemKho_CapNhat.PerformAction(Infragistics.Win.UltraWinGrid.UltraGridAction.EnterEditMode, false, false);

            //                }
            //                else
            //                {
            //                    ParentRowIndex = ParentRowIndex + 1;
            //                    if (ParentRowIndex < parentrowcount)
            //                    {
            //                        gridKiemKho_CapNhat.ActiveCell = gridKiemKho_CapNhat.Rows[ParentRowIndex].ChildBands[0].Rows[0].Cells[gridKiemKho_CapNhat.ActiveCell.Column.Key];
            //                        gridKiemKho_CapNhat.Rows[ParentRowIndex].ChildBands[0].Rows[0].Cells[gridKiemKho_CapNhat.ActiveCell.Column.Key].Activation = Activation.AllowEdit;
            //                        gridKiemKho_CapNhat.Focus();
            //                        gridKiemKho_CapNhat.PerformAction(Infragistics.Win.UltraWinGrid.UltraGridAction.EnterEditMode, false, false);
            //                    }
            //                }
            //            }
            //        }
            #endregion
        }
        private void gridKiemKho_CapNhat_Enter(object sender, EventArgs e)
        {
            gridKiemKho_CapNhat.ActiveCell = gridKiemKho_CapNhat.Rows[0].Cells["SL kiểm"];
            //gridKiemKho_CapNhat.ActiveCell = gridKiemKho_CapNhat.Rows[0].ChildBands[0].Rows[0].Cells["SL kiểm"];Số lô
            gridKiemKho_CapNhat.Rows[0].Cells["SL kiểm"].Activation = Activation.AllowEdit;
            //gridKiemKho_CapNhat.Rows[0].ChildBands[0].Rows[0].Cells["SL kiểm"].Activation = Activation.AllowEdit;
            gridKiemKho_CapNhat.PerformAction(Infragistics.Win.UltraWinGrid.UltraGridAction.EnterEditMode, false, false);
        }
        private void cboNhomHang_CapNhat_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                dtNgay_Kiem_Kho.Focus();
                dtNgay_Kiem_Kho.SelectAll();
            }
        }
        private void dtNgay_Kiem_Kho_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                txtMatHang_CapNhat.Focus();
                txtMatHang_CapNhat.SelectAll();
            }
        }

        private void txtMatHang_CapNhat_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                gridKiemKho_CapNhat.Focus();
            }
        }
        #endregion
        private void frmKiemKho_Load(object sender, EventArgs e)
        {
            KeyPreview = true;
        }
        private void btnThoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void frmKiemKho_KeyDown(object sender, KeyEventArgs e)
        {

            if (e.KeyCode == Keys.F5)
                LoadgridKiemKho(-1);
            else
                if (e.KeyCode == Keys.Escape)
                    btnThoat_Click(null, null);
                else
                    if (e.KeyCode == Keys.F2)
                    {
                        if (DoiTuong == 0)
                            btnTaoPhieu_Click(null, null);
                        else
                            btnCapNhat_Click(null, null);
                    }
        }
        private void tabKiemKho_SelectedTabChanged(object sender, Infragistics.Win.UltraWinTabControl.SelectedTabChangedEventArgs e)
        {
            if (tabKiemKho.Tabs["XuatPhieu"].Selected == true)
            {
                DoiTuong = 0;
                dtNgay_Kiem_Kho.Value = DateTime.Today;
                cboNhomHangHoa.Select();
                LoadgridKiemKho(-1);
                LoadComboboxNhomHH();
            }
            else
                if (tabKiemKho.Tabs["CapNhat"].Selected == true)
                {
                    DoiTuong = 1;
                    this.dtNgay_Kiem_Kho.FormatProvider = new System.Globalization.CultureInfo("vi-VN");
                    cboNhomHang_CapNhat.Select();
                    LoadgridKiemKho_CapNhat();
                    LoadcboNhomHang_CapNhat();
                }
        }


        private void btnThoatCapNhat_Click(object sender, EventArgs e)
        {
            this.Close();
        }
     

    }
}
